package com.intramirror.product.utils;

import com.intramirror.product.constant.NaturalNumberConstant;
import com.intramirror.product.vo.ExcelModelVO;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

/**
 * @Author luohongchao
 * @Date 下午3:29:49
 * 
 */
public class ExcelUtil {

	private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtil.class);

	/**
	 * 功能描述:excel生成工具
	 */
	public static <T> void toExcelOutStream(OutputStream out,
			String codedFileName, List<ExcelVo> header, List<T> datas) {
		try {
			String oldCodedFileName = codedFileName;
			// 产生工作簿对象
			SXSSFWorkbook workbook = new SXSSFWorkbook(NaturalNumberConstant.NATURAL_100);
			// 产生工作表对象
			int sheetNum = 1;
			Sheet sheet = workbook
					.createSheet(codedFileName + "-" + sheetNum++);
			// 进行转码，使其支持中文文件名
			codedFileName = URLEncoder
					.encode(codedFileName,"UTF-8");
			generateExcelHeader(sheet, header, workbook);
			// 插入数据
			Field field;
			int sheetLength = 0;
			int rowNum = 1;
			for (int i = 1; i <= datas.size(); i++) {
				T obj = datas.get(i - 1);
				if (obj != null) {
					// 加入超过单个Sheet的最大长度，则新城一个新的sheet页
					sheetLength++;
					if (sheetLength >= NaturalNumberConstant.NATURAL_65535) {
						sheetLength = 0;
						rowNum = 1;
						sheet = workbook.createSheet(oldCodedFileName + "-"
								+ sheetNum++);
						generateExcelHeader(sheet, header, workbook);
					}
					Row row = sheet.createRow(rowNum++);// 创建一行
					for (int j = 0; j < header.size(); j++) {
						Cell cell = row.createCell(j);// 创建一列
						cell.setCellType(header.get(j).getRowType());
						try {
							field = obj.getClass().getDeclaredField(
									header.get(j).getRowNameEg());
						} catch (NoSuchFieldException e) {
							field = obj.getClass().getSuperclass().getDeclaredField(
									header.get(j).getRowNameEg());
						}

						field.setAccessible(true);
						Object value=new Object();
						if(field.get(obj)!=null&&!"".equals(field.get(obj))){
						if(field.getType().getSimpleName().equals("Date")){
							 value = field.get(obj);
							 SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
							 value=format.format(value);

						}else{
							value = field.get(obj);
						}
						setCellValue(cell, value, header.get(j).getRowType());
						}
					}
				}
			}
			workbook.write(out);
			out.flush();
		} catch (Exception e) {
			LOGGER.error(e.getMessage(), e);
		} finally {
			try {
				if (out != null) {
					out.close();
				}
			} catch (IOException e) {
				LOGGER.error(e.getMessage(), e);
			}
		}
	}

	public static ExcelModelVO createExcel(String prefix, String suffix){
		ExcelModelVO excelModelVO = new ExcelModelVO();
		SXSSFWorkbook workbook = new SXSSFWorkbook(NaturalNumberConstant.NATURAL_100);
		excelModelVO.setWorkbook(workbook);
		excelModelVO.setRowNum(0);
		excelModelVO.setSheetLength(0);
		excelModelVO.setPreNamefix(prefix);
		excelModelVO.setSufNamefix(suffix);

		return excelModelVO;
	}

	public static <T> void writeWorkbook(ExcelModelVO excelModel, List<ExcelVo> header, List<T> datas){
		// 插入数据
		SXSSFWorkbook workbook = excelModel.getWorkbook();
		int sheetNum = excelModel.getSheetNum();
		int sheetLength = excelModel.getSheetLength();
		int rowNum = excelModel.getRowNum();
		String fileName = excelModel.getPreNamefix();
		Field field;
		Sheet sheet;

		if(sheetNum == 0){
			sheet = workbook.createSheet(fileName + "-" + sheetNum++);
			generateExcelHeader(sheet, header, workbook);
			rowNum++;
		}else{
			sheet = workbook.getSheetAt(sheetNum-1);
		}
		try {
			for (int i = 1; i <= datas.size(); i++) {
				T obj = datas.get(i - 1);
				if (obj != null) {
					// 加入超过单个Sheet的最大长度，则新城一个新的sheet页
					sheetLength++;
					if (sheetLength >= NaturalNumberConstant.NATURAL_65535) {
						sheetLength = 0;
						rowNum = 1;
						sheet = workbook.createSheet(fileName + "-"
								+ sheetNum++);
						generateExcelHeader(sheet, header, workbook);
					}
					Row row = sheet.createRow(rowNum++);// 创建一行
					for (int j = 0; j < header.size(); j++) {
						Cell cell = row.createCell(j);// 创建一列
						cell.setCellType(header.get(j).getRowType());
						try {
							field = obj.getClass().getDeclaredField(
									header.get(j).getRowNameEg());
						} catch (NoSuchFieldException e) {
							field = obj.getClass().getSuperclass().getDeclaredField(
									header.get(j).getRowNameEg());
						}

						field.setAccessible(true);
						Object value=new Object();
						if(field.get(obj)!=null&&!"".equals(field.get(obj))){
							if(field.getType().getSimpleName().equals("Date")){
								value = field.get(obj);
								SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
								value=format.format(value);

							}else{
								value = field.get(obj);
							}
							setCellValue(cell, value, header.get(j).getRowType());
						}
					}
				}
			}

			excelModel.setSheetLength(sheetLength);
			excelModel.setRowNum(rowNum);
			excelModel.setSheetNum(sheetNum);
		}catch (Exception e){
			LOGGER.error(e.getMessage(), e);
		}
	}

	public static void writeOut(ExcelModelVO excelModel, OutputStream out){

		SXSSFWorkbook workbook = excelModel.getWorkbook();
		try{
			workbook.createSheet("sheet1");
			workbook.write(out);
			out.flush();
		} catch (Exception e) {
			LOGGER.error(e.getMessage(), e);
		} finally {
			try {
				if (out != null) {
					out.close();
				}
			} catch (IOException e) {
				LOGGER.error(e.getMessage(), e);
			}
		}
	}
	
	public static void generateExcelHeader(Sheet sheet, List<ExcelVo> header,
			SXSSFWorkbook workbook) {
		// 单元格样式
		CellStyle headerStyle = workbook.createCellStyle();
		// 字体样式
		Font font = workbook.createFont();
		font.setFontHeightInPoints((short) NaturalNumberConstant.NATURAL_12);
//		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		headerStyle.setFont(font);
		// 生成头行
		if (header != null && header.size() > 0) {
			Row row = sheet.createRow(0);// 创建一行
			for (int i = 0; i < header.size(); i++) {
				Cell cell = row.createCell(i);// 创建一列
				cell.setCellType(header.get(i).getRowType());
				cell.setCellValue(header.get(i).getRowNameCn());
				cell.setCellStyle(headerStyle);
				sheet.setColumnWidth(i, header.get(i).getCellWith());
			}
		}
	}

	/**
	 * @param response
	 *            响应流
	 * @param codedFileName
	 *            文件名称，默认生成时间戳格式
	 * @param header
	 *            头行数据
	 * @param datas
	 *            表格数据
	 * @throws Exception 
	 */
	public static <T> void toExcelOutStream(HttpServletResponse response,
			String codedFileName, List<ExcelVo> header, List<T> datas) throws Exception {
		// 生成提示信息，
		response.setContentType("application/vnd.ms-excel");
		response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode(codedFileName, "UTF-8") + ".xlsx");
		try {
			toExcelOutStream(response.getOutputStream(), codedFileName, header,
					datas);
		} catch (IOException e) {
			LOGGER.error(e.getMessage(), e);
		}
	}

	/**
	 * @param cell
	 *            单元格
	 * @param value
	 *            值
	 * @param cellType
	 *            单元格类型 设置单元格的值
	 */
	public static void setCellValue(Cell cell, Object value, int cellType) {
		if (cellType == HSSFCell.CELL_TYPE_STRING) {
			cell.setCellValue(String.valueOf(value));
		} else if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
			cell.setCellValue(Double.valueOf(String.valueOf(value)));
		} else if (cellType == HSSFCell.CELL_TYPE_BOOLEAN) {
			cell.setCellValue((Boolean) value);
		} else if (cellType == HSSFCell.CELL_TYPE_BLANK) {
			cell.setCellValue("");
		}
	}

	/**
	 * 验证传入excel列数是否对应
	 * @param importExcel
	 */
	public static String validateImportHeader(ImportExcel importExcel,Class clazz){
		String errorLog = "";
		int excelHeaderNum = 0;
		List<String> titleList = new ArrayList<>();
		Row firstRow = importExcel.getRow(0);
		//获取第一行列数
		int firstRowNum = firstRow.getPhysicalNumberOfCells();
		//获取带有ExcelField注解的参数数量
		Field[] declaredFields = clazz.getDeclaredFields();
		for(Field thisField:declaredFields){
			ExcelField excelField = thisField.getAnnotation(ExcelField.class);
			if(null != excelField){
				String thisTitle = excelField.title();
				titleList.add(thisTitle);
				excelHeaderNum ++;
			}
		}
		Iterator<Cell> cellIterator = firstRow.cellIterator();
		while (cellIterator.hasNext()){
			String thisCellValue = cellIterator.next().getStringCellValue();
			if(!titleList.contains(thisCellValue)){
				errorLog += thisCellValue + ",";
			}
		}
		if(StringUtils.isNotEmpty(errorLog)){
			errorLog = errorLog.substring(0,errorLog.length()-1) + " 不在字段列表中";
		}else{
			//判断列数是否一样
			if(excelHeaderNum != excelHeaderNum){
				errorLog += "表头的列数无法对应，请检查导入的数据";
			}
		}
		return errorLog;
	}

	/**
	 * @Author luohongchao
	 * @Date 下午3:30:28 生成excel所需的头信息
	 */
	public static class ExcelVo {
		public ExcelVo(String rowNameCn, int rowType, String rowNameEg) {
			this.rowNameCn = rowNameCn;
			this.rowNameEg = rowNameEg;
			this.rowType = rowType;
		}

		/**
		 * 字段中文描述
		 */
		private String rowNameCn;
		/**
		 * 字段类型
		 * */
		private int rowType;
		/**
		 * 字段名
		 * */
		private String rowNameEg;
		/**
		 * 列宽
		 * */
		private int cellWith = NaturalNumberConstant.NATURAL_25 * NaturalNumberConstant.NATURAL_256;

		public String getRowNameCn() {
			return rowNameCn;
		}

		public void setRowNameCn(String rowNameCn) {
			this.rowNameCn = rowNameCn;
		}

		public int getRowType() {
			return rowType;
		}

		public void setRowType(int rowType) {
			this.rowType = rowType;
		}

		public String getRowNameEg() {
			return rowNameEg;
		}

		public void setRowNameEg(String rowNameEg) {
			this.rowNameEg = rowNameEg;
		}

		public int getCellWith() {
			return cellWith;
		}

		public void setCellWith(int cellWith) {
			this.cellWith = cellWith;
		}

	}

}
